package DAO;

import icms.ORM.UserInfo;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Transaction;

public class UserDAO extends BaseDAO implements IUserDAO {
	private final String SQL_Head_List = "call List_";
	private final String SQL_Role[] = { "Root", "Admin", "Audit", "Editor",
			"Member", "User" };

	public UserInfo findUserById(Integer targetID, Integer selfClass,
			Boolean bSelf) throws SQLException {
		// TODO Auto-generated method stub
		String sql = null;
		if (selfClass == 0 && bSelf == false) {
			sql = "List_admin_by_id_as_root";
		}
		if (selfClass == 1 && bSelf == false) {
			sql = "List_user_by_id_as_admin";
		} else if (bSelf == true) {
			sql = "List_user_by_id_as_self";
		}
		Query query = session.getNamedQuery(sql);
		query.setInteger(0, targetID);
		List<?> list = query.list();
		// session.close();
		if (list != null && list.size() != 0)
			return (UserInfo) list.get(0);
		return null;
	}

	public Boolean saveUser(UserInfo user, Integer selfClass) throws Exception {
		String sql = null;
		// this.saveOrUpdate(user);
		if (selfClass == 4) {
			sql = "call Add_member_as_member(?,?,?,?,?,?,?,?);";
			Transaction tx = session.beginTransaction();
			Connection con = session.connection();
			CallableStatement cstmt = con.prepareCall(sql);
			cstmt.setString(1, user.getUserName());
			cstmt.setString(2, user.getUserPwd());
			cstmt.setString(3, user.getUserRealName());
			cstmt.setString(4, user.getUserEmail());
			cstmt.setString(5, user.getUserTelephone());
			cstmt.setInt(6, user.getUserThemeID());
			cstmt.setInt(7, user.getUserDisable());
			cstmt.setString(8, "@err");

			// Var For Debug
			Integer flag = cstmt.executeUpdate();

			tx.commit();
			System.out.println(flag.toString());
		} else if (selfClass == 1) {
			sql = "call {Add_auditorAndeditor_as_admin(?,?,?,?,?,?,?,?,?,?)}";
		} else if (selfClass == 0) {
			sql = "call {Add_admin_as_root(?,?,?,?,?,?,?,?,?)}";
		} else {
			return false;
		}

		/*
		 * Date currentTime = new Date(); SimpleDateFormat formatter = new
		 * SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String dateString =
		 * formatter.format(currentTime); user.setUserRegTime(dateString);
		 * session.beginTransaction();
		 * 
		 * session.save(user);
		 * 
		 * session.getTransaction().commit();
		 * 
		 * session.close();
		 */
		return true;

	}

	public Boolean deleteUser(Integer targetID, Integer selfID)
			throws SQLException {
		// TODO Auto-generated method stub
		String sql = null;
		// this.saveOrUpdate(user);
		if (targetID == selfID) {
			sql = "call Delete_member_by_id_as_self(?,?);";
			Transaction tx = session.beginTransaction();
			Connection con = session.connection();
			CallableStatement cstmt = con.prepareCall(sql);
			cstmt.setInt(1, targetID);
			cstmt.setString(2, "@err");

			// Var For Debug
			Integer flag = cstmt.executeUpdate();
			tx.commit();
			System.out.println(flag.toString());
		} else {
			return false;
		}
		return true;
	}

	public Boolean updateUser(UserInfo userInfo, Integer selfClass, Boolean self)
			throws SQLException {
		String sql = null;
		if (self == true) {
			sql = "call Modify_user_by_id_as_self(?,?,?,?,?,?);";
			Transaction tx = session.beginTransaction();
			Connection con = session.connection();
			CallableStatement cstmt = con.prepareCall(sql);
			cstmt.setInt(1, userInfo.getUserID());
			cstmt.setString(2, userInfo.getUserPwd());
			cstmt.setString(3, userInfo.getUserRealName());
			cstmt.setString(4, userInfo.getUserEmail());
			cstmt.setString(5, userInfo.getUserTelephone());
			if (userInfo.getUserThemeID() != null) {
				cstmt.setInt(6, userInfo.getUserThemeID());
			} else {
				userInfo.setUserThemeID(0);
				cstmt.setInt(6, userInfo.getUserThemeID());
			}

			// Var For Debug
			Integer flag = cstmt.executeUpdate();
			tx.commit();
			System.out.println(flag.toString());
			System.out.println("Info :: updateUser@UserDAO :: DONE");

		} else if (selfClass == 1) {
			sql = "call Modify_user_by_id_as_self(?,?,?,?,?,?);";
			Transaction tx = session.beginTransaction();
			Connection con = session.connection();
			CallableStatement cstmt = con.prepareCall(sql);
			cstmt.setInt(1, userInfo.getUserID());
			cstmt.setString(2, userInfo.getUserPwd());
			cstmt.setString(3, userInfo.getUserRealName());
			cstmt.setString(4, userInfo.getUserEmail());
			cstmt.setString(5, userInfo.getUserTelephone());
			if (userInfo.getUserThemeID() != null) {
				cstmt.setInt(6, userInfo.getUserThemeID());
			} else {
				userInfo.setUserThemeID(0);
				cstmt.setInt(6, userInfo.getUserThemeID());
			}

			// Var For Debug
			Integer flag = cstmt.executeUpdate();
			tx.commit();
			System.out.println(flag.toString());
			System.out.println("Info :: updateUser@UserDAO :: DONE");

		} else if (selfClass == 0) {
			sql = "call Modify_user_by_id_as_self(?,?,?,?,?,?);";
			Transaction tx = session.beginTransaction();
			Connection con = session.connection();
			CallableStatement cstmt = con.prepareCall(sql);
			cstmt.setInt(1, userInfo.getUserID());
			cstmt.setString(2, userInfo.getUserPwd());
			cstmt.setString(3, userInfo.getUserRealName());
			cstmt.setString(4, userInfo.getUserEmail());
			cstmt.setString(5, userInfo.getUserTelephone());
			if (userInfo.getUserThemeID() != null) {
				cstmt.setInt(6, userInfo.getUserThemeID());
			} else {
				userInfo.setUserThemeID(0);
				cstmt.setInt(6, userInfo.getUserThemeID());
			}

			// Var For Debug
			Integer flag = cstmt.executeUpdate();
			tx.commit();
			System.out.println(flag.toString());
			System.out.println("Info :: updateUser@UserDAO :: DONE");
		} else {
			return false;
		}
		return self;

	}

	public List<UserInfo> listUser(Integer targetClass, Integer selfClass) {
		// TODO Auto-generated method stub
		String sql = null;
		if (targetClass == 1 && selfClass == 0) {
			sql = "List_admin_all_as_root";
		} else if (targetClass == 2 && selfClass == 1) {
			sql = "List_auditor_all_as_admin";
		} else if (targetClass == 3 && selfClass == 1) {
			sql = "List_editor_all_as_admin";
		} else if (targetClass == 4 && selfClass == 1){
			sql = "List_member_all_as_admin";
		}else {
			return null;
		}
		Query query = session.getNamedQuery(sql);
		//query.setInteger(0, targetID);
		List<?> list = query.list();
		// session.close();
		System.out.println("Info :: listUser@UserDAO :: list.size()="+list.size());
		if (list != null && list.size() != 0)
			return (List<UserInfo>) list;
		return null;
	}
}
